﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using BE;
using NHibernate;
using NHibernate.Criterion;
using System.Data;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class ComprobanteVenta_DAL : Controlador
    {

        public DataTable listado(String Numeracion, String Nombre, DateTime fechainicio, DateTime fechaFin)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListadoComprobanteVenta";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_Numeracion", Numeracion);
                    SqlCmd.Parameters.AddWithValue("@in_nombre", Nombre);
                    SqlCmd.Parameters.AddWithValue("@in_fechainicio", fechainicio);
                    SqlCmd.Parameters.AddWithValue("@in_fechaFin", fechaFin);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable listadoDeudaPersona(String IDPersona, String Filtro)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_DeudasEmpleado";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_IDPersona", IDPersona);
                    SqlCmd.Parameters.AddWithValue("@in_Filtro", Filtro);

                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public List<ConceptoTesoreria> ObtenerConceptos()
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    ICriteria criteria = Sesion.CreateCriteria("ConceptoTesoreria")
                        .AddOrder(Order.Asc("Nombre"));
                    //criteria.SetMaxResults(100);
                    List<ConceptoTesoreria> listado = (List<ConceptoTesoreria>)criteria.List<ConceptoTesoreria>();
                    return listado;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        public ComprobanteVenta ObtenerComprobantePorId(String ID)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    ComprobanteVenta cp = (ComprobanteVenta)Sesion.CreateCriteria("ComprobanteVenta").Add(Restrictions.Eq("IDCp", Convert.ToInt32(ID))).UniqueResult();
                    return cp;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public List<TipoDocumento> ObtenerTipoDocumentos()
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    ICriteria criteria = Sesion.CreateCriteria("TipoDocumentoCpVenta")
                        .AddOrder(Order.Asc("Nombre"));
                    //criteria.SetMaxResults(100);
                    List<TipoDocumento> listado = (List<TipoDocumento>)criteria.List<TipoDocumento>();
                    return listado;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public List<Periodo> ObtenerPeriodos()
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    NHibernate.ICriteria criteria = Sesion.CreateCriteria("Periodo")
                        .AddOrder(Order.Desc("Semestre"));
                    criteria.SetMaxResults(50);
                    List<Periodo> listado = (List<Periodo>)criteria.List<Periodo>();
                    return listado;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public ConceptoTesoreria obtenerConceptoTesoreriaxId(String ID)
        {

            try
            {
                return (ConceptoTesoreria)AyudaNHibernate.ObtenerEntidadPorID("ConceptoTesoreria", ID);
            }
            catch (Exception)
            {

                throw;
            }

        }

        public Periodo obtenerPeriodoxId(String ID)
        {

            try
            {
                return (Periodo)AyudaNHibernate.ObtenerEntidadPorID("Periodo", ID);
            }
            catch (Exception)
            {

                throw;
            }

        }

        public TipoDocumentoCpVenta ObtenerTipoDocumentoXid(String ID)
        {

            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    Object Obj = (Object)Sesion.CreateCriteria("TipoDocumento").Add(Restrictions.Eq("IDTipoCp", Convert.ToInt32(ID))).UniqueResult();


                    return (TipoDocumentoCpVenta)Obj;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }


        }


        public Alumno ObtenerAlumnoXid(String ID)
        {

            try
            {
                return (Alumno)AyudaNHibernate.ObtenerEntidadPorID("Alumno", ID);
            }
            catch (Exception)
            {

                throw;
            }
        }


        public BE.SocioNegocio ObtenerSocioNegocioXid(String ID)
        {

            try
            {
                return (BE.SocioNegocio)AyudaNHibernate.ObtenerEntidadPorEtiqueta("SocioNegocio", "Codigo", ID);
            }
            catch (Exception)
            {

                throw;
            }
        }



        public void Insertar(ComprobanteVenta cp)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    try
                    {
                        Sesion.Save(cp);
                        Sesion.Flush();

                        MySqlCommand SqlCmd = new MySqlCommand();
                        SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                        Trans.Enlist(SqlCmd);

                        SqlCmd.CommandText = "sp_NumeracionDocumentos";
                        SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlCmd.Parameters.AddWithValue("@in_IDTipoDocumento", cp.TipoDocumento.IDTipoCp);
                        SqlCmd.Parameters.AddWithValue("@in_IDCp", cp.IDCp);
                        SqlCmd.Parameters.AddWithValue("@in_Tabla", cp.TipoDocumento.Entidad);
                        SqlCmd.ExecuteNonQuery();

                        SqlCmd.CommandText = "sp_GenearDeuda";
                        SqlCmd.Parameters.Clear();
                        SqlCmd.Parameters.AddWithValue("@in_IDCpVenta", cp.IDCp);

                        SqlCmd.ExecuteNonQuery();

                        foreach (ItemComprobanteVenta item in cp.Items)
                        {
                            if (Convert.ToInt32(item.idDeuda) > 0)
                            {
                                SqlCmd.CommandText = "sp_ActualizarDeuda";
                                SqlCmd.Parameters.Clear();
                                SqlCmd.Parameters.AddWithValue("@in_IDDeuda", Convert.ToInt32(item.idDeuda));
                                SqlCmd.Parameters.Add("@out_SaldoNuevo", MySqlDbType.Decimal, 50);
                                SqlCmd.Parameters["@out_SaldoNuevo"].Direction = ParameterDirection.Output;
                                SqlCmd.ExecuteNonQuery();
                                Decimal nuevosaldo = Convert.ToDecimal(SqlCmd.Parameters["@out_SaldoNuevo"].Value);

                                if (nuevosaldo < 0)
                                {
                                    throw new Exception("El monto a pagar excede el saldo");
                                }
                            }
                        }


                        Trans.Commit();

                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        throw;
                    }
                }
            }
        }


        public void Anular(Int32 idCp, String Usuario)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    try
                    {
                        ComprobanteVenta cp = ObtenerComprobantePorId(idCp.ToString());
                        if (cp.Anulado)
                        {
                            throw new Exception("El comprobante ya esta anulado desde la fecha :" + cp.FechaAnulacion.ToString());
                        }

                        cp.AnuladoPor = Usuario;
                        cp.FechaAnulacion = DateTime.Now;
                        cp.Anulado = true;
                        Sesion.Update(cp);
                        Trans.Commit();
                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public void SaldarDeuda(String in_IDCpVenta)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    try
                    {
                        MySqlCommand SqlCmd = new MySqlCommand();
                        SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                        Trans.Enlist(SqlCmd);
                        SqlCmd.CommandText = "sp_SaldarDeuda";
                        SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlCmd.Parameters.AddWithValue("@in_IDCpVenta", in_IDCpVenta);
                        SqlCmd.ExecuteNonQuery();
                        Trans.Commit();
                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        throw;
                    }

                }
            }
        }



        public DataTable Validar3omasAsignaturaMismoCiclo(String codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_Validar3omasAsignaturaMismoCiclo";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inCodigo", codigo);


                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }

        public DataTable Validar6cursoenlacarrera(String codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_Validar6cursoenlacarrera";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inCodigo", codigo);


                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


        public DataTable Validar1cursoxterceravez(String codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_Validar1cursoxterceravez";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inCodigo", codigo);


                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


    }
}
